<?php

namespace app\models;

use yii;
use yii\db\Query;
/**
 *收款表
 */
class Income extends BaseModel
{
    use \app\lib\Redis;

    public $redis;

    public function __construct()
    {
        $this->redis = $this->getInstance();
        $this->table = yii::$app->db->tablePrefix . 'income';
    }

    public function insert_income($data)
    {
        $keys = [
            'date', 'name', 'country', 'number', 'customer_name', 'service_name',
            'pay_type', 'money_type', 'invocie_num', 'in_come', 'total_fee', 'fee',
            'email', 'remark', 'pay_from'
        ];
        return yii::$app->db->createCommand()
            ->batchInsert($this->table, $keys, $data)
            ->execute();
    }

    /**带分页list
     * @param $p
     * @param $s
     * @param array $where
     * @return array
     */
    public function all($where = [], $offset = false, $limit = false, $order = false)
    {
        $db = new Query();
        $db->select('*')->from($this->table);
        if (isset($where['pay_from']) && ($where['pay_from'] > 0)) {
            $db->andwhere(['pay_from' => $where['pay_from']]);
        }
        if (!empty($where['service_name'])) {
            $db->andwhere(['service_name' => $where['service_name']]);
        }
        if (!empty($where['number'])) {
            $db->andWhere(['number' => $where['number']]);
        }
        if (!empty($where['customer_name'])) {
            $db->andwhere(['like', 'customer_name', $where['customer_name']]);
        }
        if (!empty($where['invocie_num'])) {
            $db->andwhere(['invocie_num' => $where['invocie_num']]);
        }
        if (!empty($where['pid'])) {
            $db->andwhere(['pid' => $where['pid']]);
        }
        if (!empty($where['property_p'])) {
            if ($where['property_p'] == 999) {
                $db->andWhere(['property_p' => 0]);
            } else {
                $db->andWhere(['property_p' => $where['property_p']]);
            }
        }
        if (!empty($where['begin_time'])) {
            $db->andWhere(['>=', 'date', $where['begin_time']]);
        }
        if (!empty($where['end_time'])) {
            $db->andWhere(['<=', 'date', $where['end_time']]);
        }
        if ($order) {
            $db->orderBy($order);
        }
        if ($offset) {
            $db->offset($offset);
        }
        if ($limit) {
            $db->limit($limit);
        }
        return $db->all();
    }


    /**不带分页list
     * @param array $where
     * @return array
     */
    public function list($where = [])
    {
        $db = new Query();
        $db->select('*')->from($this->table);
        if ($where['begin_time'] ?? 0) {
            $db->andWhere(['>=', 'date', $where['begin_time']]);
        }
        if ($where['end_time'] ?? 0) {
            $db->andWhere(['<=', 'date', $where['end_time']]);
        }
        if (isset($where['pay_from']) && ($where['pay_from'] > 0)) {
            $db->andwhere(['pay_from' => $where['pay_from']]);
        }
        if (!empty($where['service_name'])) {
            $db->andwhere(['service_name' => $where['service_name']]);
        }
        if ($where['number']) {
            $db->andWhere(['number' => $where['number']]);
        }
        if (!empty($where['customer_name'])) {
            $db->andwhere(['like', 'customer_name', $where['customer_name']]);
        }
        if (!empty($where['invocie_num'])) {
            $db->andwhere(['invocie_num' => $where['invocie_num']]);
        } else {
            $db->andwhere(['pid' => 0]);
        }
        if ($where['property_p']) {
            if ($where['property_p'] == 999) {
                $db->andWhere(['property_p' => 0]);
            } else {
                $db->andWhere(['property_p' => $where['property_p']]);
            }
        }
        $list = $db->orderBy(['date' => SORT_DESC, 'id' => SORT_DESC])->all();
        return $list;
    }


    public function get_caculate_data($where = [])
    {
        $db = new Query();
        $db->select(['SUM(in_come) as in_come,SUM(total_fee) as total_fee,SUM(fee) as fee,SUM(clean_fee) as clean_fee'])
            ->from($this->table);
        if ($where['begin_time'] ?? 0) {
            $db->andWhere(['>=', 'date', $where['begin_time']]);
        }
        if ($where['end_time'] ?? 0) {
            $db->andWhere(['<=', 'date', $where['end_time']]);
        }

        if (isset($where['pay_from']) && ($where['pay_from'] > 0)) {
            $db->andWhere(['pay_from' => $where['pay_from']]);
        }
        if (!empty($where['service_name'])) {
            $db->andwhere(['service_name' => $where['service_name']]);
        }
        if ($where['number']) {
            $db->andWhere(['number' => $where['number']]);
        }
        if (!empty($where['customer_name'])) {
            $db->andwhere(['like', 'customer_name', $where['customer_name']]);
        }
        if (!empty($where['invocie_num'])) {
            $db->andwhere(['invocie_num' => $where['invocie_num']]);
        } else {
            $db->andWhere(['pid' => 0]);
        }
        if ($where['property_p']) {
            if ($where['property_p'] == 999) {
                $db->andWhere(['property_p' => 0]);
            } else {
                $db->andWhere(['property_p' => $where['property_p']]);
            }
        }
        $list = $db->one();
        return $list;
    }

    public function chartData($where)
    {
        $db = new Query();
        $db->select(['SUM(in_come+total_fee+fee) as value,property_p'])
            ->from($this->table);
        if ($where['begin_time'] ?? 0) {
            $db->andWhere(['>=', 'date', $where['begin_time']]);
        }
        if ($where['end_time'] ?? 0) {
            $db->andWhere(['<=', 'date', $where['end_time']]);
        }

        if (isset($where['pay_from']) && ($where['pay_from'] > 0)) {
            $db->andWhere(['pay_from' => $where['pay_from']]);
        }
        if (!empty($where['service_name'])) {
            $db->andwhere(['service_name' => $where['service_name']]);
        }
        if ($where['number']) {
            $db->andWhere(['number' => $where['number']]);
        }
        if (!empty($where['customer_name'])) {
            $db->andwhere(['like', 'customer_name', $where['customer_name']]);
        }
        if (!empty($where['invocie_num'])) {
            $db->andwhere(['invocie_num' => $where['invocie_num']]);
        } else {
            $db->andWhere(['pid' => 0]);
        }
        if ($where['property_p']) {
            if ($where['property_p'] == 999) {
                $db->andWhere(['property_p' => 0]);
            } else {
                $db->andWhere(['property_p' => $where['property_p']]);
            }
        }
        $list = $db->groupBy('property_p')->all();
        return $list;
    }

    public function get_income_size($where = [])
    {
        $db = new Query();
        $db->select(['count(*) as num'])->from($this->table);
        if ($where['begin_time'] ?? 0) {
            $db->andWhere(['>=', 'date', $where['begin_time']]);
        }
        if ($where['end_time'] ?? 0) {
            $db->andWhere(['<=', 'date', $where['end_time']]);
        }
        if (isset($where['pay_from']) && ($where['pay_from'] > 0)) {
            $db->andWhere(['pay_from' => $where['pay_from']]);
        }
        if (!empty($where['service_name'])) {
            $db->andwhere(['service_name' => $where['service_name']]);
        }
        if ($where['number']) {
            $db->andWhere(['number' => $where['number']]);
        }
        if (!empty($where['customer_name'])) {
            $db->andwhere(['like', 'customer_name', $where['customer_name']]);
        }
        if (!empty($where['invocie_num'])) {
            $db->andwhere(['invocie_num' => $where['invocie_num']]);
        }
        if ($where['property_p']) {
            if ($where['property_p'] == 999) {
                $db->andWhere(['property_p' => 0]);
            } else {
                $db->andWhere(['property_p' => $where['property_p']]);
            }
        }
        $list = $db->one();
        return $list ? $list['num'] : 0;
    }

    public function one($where = [])
    {
        $db = new Query();
        $db->select('*')->from($this->table);
        if (!empty($where['id'])) {
            $db->andWhere(['id' => $where['id']]);
        }
        if (!empty($where['pid'])) {
            $db->andWhere(['pid' => $where['pid']]);
        }
        return $db->limit(1)->one();
    }

    /**销售汇总
     * @param $year
     * @return array
     */
    public function sell_performance($year)
    {
        //invoice为空 样品费
        $sql = "SELECT * FROM (SELECT DATE_FORMAT( date,  '%Y-%m' ) AS 
		MONTH , SUM( in_come+total_fee+fee ) as money ,4 as pay_from, service_name
		FROM  `jingsourcing_income` 
		WHERE service_name>0 AND property_p=18 AND pid=id AND DATE_FORMAT(date,'%Y')=" . $year . "
		GROUP BY MONTH , pay_from, service_name
		UNION ALL
		SELECT DATE_FORMAT( date,  '%Y-%m' ) AS 
		MONTH , SUM( in_come+total_fee+fee ) as money,pay_from, service_name
		FROM  `jingsourcing_income`
		WHERE service_name>0 AND property_p = 17 AND pid=id AND DATE_FORMAT(date,'%Y')=" . $year . "
		GROUP BY MONTH ,pay_from,service_name
		) as a
		ORDER BY service_name ASC , 
		MONTH DESC
		";
        return yii::$app->db->createCommand($sql)->queryAll();
    }

    public function get_list_by_service_and_month($service_name, $month)
    {
        $db = new Query();
        return $db->select('*')->from($this->table)->where(['service_name' => $service_name, 'split_status' => 1, "date_format(date,'%Y-%m')" => $month])
            ->orderBy(['date' => SORT_DESC])->all();

    }

    public function get_all_year_list_by_service($service_name)
    {
        $db = new Query();
        $date = date('Y') . '-01';
        return $db->select('id,name,remark,country,customer_name,date,invocie_num,sum(in_come) as in_come,sum(total_fee) as total_fee,sum(fee) as fee')
            ->from($this->table)
            ->where(['service_name' => $service_name])
            ->andWhere(['!=', 'invocie_num', ''])
            ->andWhere(['>', 'date', $date])
            ->groupBy('invocie_num')
            ->orderBy(['date' => SORT_DESC])
            ->all();
    }


    //统计样品费
    public function sample_fee_list($p, $s, $where = [])
    {
        $db = new Query();
        $db->select('customer_name,number,service_name,sum(in_come) as in_come,sum(total_fee) as total_fee,sum(fee) as fee')
            ->from($this->table)
            ->where(['property_p' => 18]);
        if ($where['service_name']) {
            $db->andWhere(['service_name' => $where['service_name']]);
        }
        if ($where['number']) {
            $db->andWhere(['number' => $where['number']]);
        }
        if ($where['customer_name']) {
            $db->andWhere(['customer_name' => $where['customer_name']]);
        }
        $list = $db->groupBy('number')
            ->orderBy(['number' => SORT_DESC])
            ->offset(($p - 1) * $s)
            ->limit($s)
            ->all();
        return $list;
    }

    /**样品费size
     * @param array $where
     * @return int
     */
    public function sample_fee_size($where = [])
    {
        $db = new Query();
        $db->select('count(number) as num')
            ->from($this->table)
            ->where(['invocie_num' => '']);
        if ($where['service_name']) {
            $db->andWhere(['service_name' => $where['service_name']]);
        }
        if ($where['number']) {
            $db->andWhere(['number' => $where['number']]);
        }
        if ($where['customer_name']) {
            $db->andWhere(['customer_name' => $where['customer_name']]);
        }
        $db->groupBy('number');

        $query = new Query();
        $info = $query->select('count(*) as num')
            ->from(['a' => $db])
            ->one();
        return $info ? $info['num'] : 0;
    }

    //通过invoice获取收入记录
    public function get_income_by_invoice($invoice)
    {
        if (!$invoice) {
            return false;
        }
        $db = new Query();
        $db->select('*')->from($this->table);
        if (is_array($invoice)) {
            $db->andWhere(['in', 'invocie_num', $invoice]);
        } else {
            $db->andWhere(['invocie_num' => $invoice]);
        }

        return $db->all();
    }

    //获取所有invoice获取收入记录分组操作
    public function get_group_income_by_invoice($in)
    {
        $query = new Query();
        $query->select('date,invocie_num,in_come,total_fee,fee')
            ->from($this->table)
            ->where(['in', 'invocie_num', $in])
            ->orderBy(['invocie_num' => SORT_DESC, 'in_come' => SORT_DESC]);
        $db = new Query();
        $arr = $db->select('group_concat(date order by in_come desc) as date,invocie_num,sum(in_come) as in_come,sum(total_fee) as total_fee,sum(fee) as fee')->from(['a' => $query])
            ->groupBy('invocie_num')
            ->orderBy(['invocie_num' => SORT_DESC, 'in_come' => SORT_DESC])
            ->all();
        $tmp = [];
        foreach ($arr as $k => $v) {
            $v['date'] = explode(',', $v['date'])[0];
            $tmp[$v['invocie_num']] = $v;
        }
        return $tmp;
    }

    public function get_sum_income_by_month($m)
    {
        $begin = $m . '-01';
        $end = $m . '-31';
        $db = new Query();
        return $db->select('sum(in_come+total_fee+fee) as income')
            ->from($this->table)
            ->andWhere(['split_status' => 1])
            ->andWhere(['between', 'date', $begin, $end])
            ->andWhere(['>', 'in_come', 0])
            ->one();
    }

    public function get_sum_income_by_year($m)
    {
        $begin = date('Y', strtotime($m)) . '-01-01';
        $end = date('Y-m', strtotime($m)) . '-31';
        $db = new Query();
        $list = $db->select(["date_format(date,'%Y-%m') as mon", "sum(in_come+total_fee+fee) as income"])
            ->from($this->table)
            ->andWhere(['between', 'date', $begin, $end])
            ->andWhere(['split_status' => 1])
            ->andWhere(['>', 'in_come', 0])
            ->groupBy(["date_format(date,'%Y-%m')"])
            ->all();
        $arr = [];
        foreach ($list as $v) {
            $arr[$v['mon']] = $v;
        }
        unset($list);
        return $arr;
    }

    /**
     * 查看流水编号
     */
    public function list_by_serial_num($p, $s, $where = [])
    {
        $query = new Query();
        $query->select('id,serial_num,date,name,country,number,customer_name,service_name,pay_type
        ,money_type,group_concat(invocie_num) as invocie_num,email,group_concat(remark) as remark,pay_from,
        ,sum(in_come) as in_come,sum(total_fee) as total_fee,sum(fee) as fee')->from($this->table)
            ->groupBy('serial_num');
        $db = new Query();
        $db->select("*")->from(['a' => $query]);
        if ($where['begin_time'] ?? 0) {
            $db->andWhere(['>=', 'a.date', $where['begin_time']]);
        }
        if ($where['end_time'] ?? 0) {
            $db->andWhere(['<=', 'a.date', $where['end_time']]);
        }
        if (isset($where['pay_from']) && ($where['pay_from'] > 0)) {
            $db->andwhere(['pay_from' => $where['pay_from']]);
        }
        if (!empty($where['service_name'])) {
            $db->andwhere(['service_name' => $where['service_name']]);
        }
        if (!empty($where['number'])) {
            $db->andwhere(['like', 'customer_name', $where['number']]);
        }
        if (!empty($where['invocie_num'])) {
            $db->andwhere(['like', 'invocie_num', $where['invocie_num']]);
        }
        $list = $db->orderBy(['date' => SORT_DESC, 'id' => SORT_DESC])
            ->offset(($p - 1) * $s)
            ->limit($s)
            ->all();
        return $list;
    }

    public function size_by_serial_num($where = [])
    {
        $query = new Query();
        $query->select('id,serial_num,date,name,country,number,customer_name,service_name,pay_type
        ,money_type,group_concat(invocie_num) as invocie_num,email,group_concat(remark) as remark,pay_from,
        ,sum(in_come) as in_come,sum(total_fee) as total_fee,sum(fee) as fee')->from($this->table)
            ->groupBy('serial_num');
        $db = new Query();
        $db->select('count(*) as num')->from(['a' => $query]);
        if ($where['begin_time'] ?? 0) {
            $db->andWhere(['>=', 'a.date', $where['begin_time']]);
        }
        if ($where['end_time'] ?? 0) {
            $db->andWhere(['<=', 'a.date', $where['end_time']]);
        }
        if (isset($where['pay_from']) && ($where['pay_from'] > 0)) {
            $db->andwhere(['pay_from' => $where['pay_from']]);
        }
        if (!empty($where['service_name'])) {
            $db->andwhere(['service_name' => $where['service_name']]);
        }
        if (!empty($where['number'])) {
            $db->andwhere(['like', 'customer_name', $where['number']]);
        }
        if (!empty($where['invocie_num'])) {
            $db->andwhere(['like', 'invocie_num', $where['invocie_num']]);
        }

        return $db->one();

    }

    public function caculate_by_serial_num($where = [])
    {
        $query = new Query();
        $query->select('id,serial_num,date,name,country,number,customer_name,service_name,pay_type
        ,money_type,group_concat(invocie_num) as invocie_num,email,group_concat(remark) as remark,pay_from,
        ,sum(in_come) as in_come,sum(total_fee) as total_fee,sum(fee) as fee')->from($this->table)
            ->groupBy('serial_num');
        $db = new Query();
        $db->select(['SUM(in_come) as in_come,SUM(total_fee) as total_fee,SUM(fee) as fee'])->from(['a' => $query]);
        if ($where['begin_time'] ?? 0) {
            $db->andWhere(['>=', 'a.date', $where['begin_time']]);
        }
        if ($where['end_time'] ?? 0) {
            $db->andWhere(['<=', 'a.date', $where['end_time']]);
        }
        if (isset($where['pay_from']) && ($where['pay_from'] > 0)) {
            $db->andwhere(['pay_from' => $where['pay_from']]);
        }
        if (!empty($where['service_name'])) {
            $db->andwhere(['service_name' => $where['service_name']]);
        }
        if (!empty($where['number'])) {
            $db->andwhere(['like', 'customer_name', $where['number']]);
        }
        if (!empty($where['invocie_num'])) {
            $db->andwhere(['like', 'invocie_num', $where['invocie_num']]);
        }
        return $db->one();
    }

    /**
     * 复制一条记录
     * @param int $id
     * @return int 插入记录id
     */
    public function copy_record($id)
    {
        $db = new Query();
        $info = $db->select('*')->from($this->table)->where(['id' => $id])->limit(1)->one();
        unset($info['id']);
        yii::$app->db->createCommand()->insert($this->table, $info)->execute();
        return yii::$app->db->getLastInsertID();
    }

    /**
     * 更新记录
     */
    public function update_record($data, $id)
    {
        $data['split_status'] = 2;
        return yii::$app->db->createCommand()->update($this->table, $data, ['id' => $id])->execute();
    }

    /**获取拆分项
     * @param $arr
     * @param array $where
     * @return array
     *
     */
    public function serialArr($arr)
    {
        $db = new Query();
        $db->select('*')->from($this->table);
        $db->andWhere(['in', 'pid', $arr]);
        return $db->all();
    }

    /**获取每个月的总计数据
     * @param $year
     */
    public function sumIncomeEveryMonth($year, $where = [])
    {
        $db = new Query();
        $db->select(["DATE_FORMAT(`date`,'%Y-%m') AS m", "sum(in_come+total_fee+fee) as income"])
            ->from($this->table)
            ->andWhere('id=pid');
        if (!empty($where['staff'])) $db->andWhere(['service_name' => $where['staff']]);
        $list = $db->andWhere(['!=', 'service_name', 999])
            ->andWhere(['>=', 'date', $year . '-01-01'])
            ->andWhere(['<=', 'date', $year . '-12-31'])
            ->groupBy('m')->all();
        return $list;
    }


    public function sumIncomebyMonth($month, $where = [])
    {
        $db = new Query();
        $db->select('sum(in_come+total_fee+fee) as income,service_name')
            ->from($this->table)
            ->andWhere('id=pid');
        if (!empty($where['staff'])) $db->andWhere(['service_name' => $where['staff']]);

        return $db->andWhere(['between', 'date', $month . '-01', $month . '-31'])
            ->andWhere(['>', 'in_come', 0])
            ->andWhere(['property_p' => 17])
            ->groupBy('service_name')
            ->all();
    }

    public function validateMoney($pid)
    {
        $list = $this->all(['pid' => $pid]);
        $sum2 = $sum1 = ['in_come' => 0, 'total_fee' => 0, 'fee' => 0];
        $status = 0;
        foreach ($list as $v) {
            if ($v['id'] == $v['pid']) {
                $sum1['in_come'] = $v['in_come'];
                $sum1['total_fee'] = $v['total_fee'];
                $sum1['fee'] = $v['fee'];
            } else {
                $status = 1;
                $sum2['in_come'] += $v['in_come'];
                $sum2['total_fee'] += $v['total_fee'];
                $sum2['fee'] += $v['fee'];
            }
        }
        if (
            (
                round($sum1['in_come'], 2) != round($sum2['in_come'], 2)
                ||
                round($sum1['total_fee'], 2) != round($sum2['total_fee'], 2)
                ||
                round($sum1['fee'], 2) != round($sum2['fee'], 2)
            )
            && $status == 1

        ) {
            foreach ($list as $v) {
                $this->save(['moneySame' => false], ['id' => $v['id']]);
            }
        } else {
            foreach ($list as $v) {
                $this->save(['moneySame' => true], ['id' => $v['id']]);
            }
        }
    }
}
